/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package commun;

import java.sql.*;
import java.io.*;

import java.util.ArrayList;
/**
 *
 * @author Qiao Bingzhi
 */
public class DBManager {

    private static String driver="org.sqlite.JDBC"; //driver database
    private static String url="jdbc:sqlite:";              //datebase URL
    private static String dbFileName="venus.db";
    private Connection connect=null;                    // database connect
    private Statement stat=null;
    private ResultSet result=null;
 
  static
  {
  try
  {
      Class.forName(driver);     // load the driver    
  }
  catch(Exception e)
  {
     e.printStackTrace();
  }
 }

  public DBManager() 
   {
      if(!dbExist())                                             //db no exist
      {
        try
        {
        //connect=DriverManager.getConnection(url+";create=true"); //create the db
           System.out.println("DB not ok");
        }
        catch(Exception e)
        {
          e.printStackTrace();
        }
      }
      else
      {
          connect=connect();   //connect db
          System.out.println(connect);
          if(connect!=null)
          {
           try{
          stat=connect.createStatement();
           }
           catch(Exception e)
           {
             e.printStackTrace();
           }    
         
          }    
      }
  
  }

  private boolean dbExist()      // test the db
  {
     boolean exist =false;
     File dbDir =new File(dbFileName);
   //  System.out.println(dbDir);
   //  System.out.println(dbDir.exists());

     if(dbDir.exists())
     {
        exist=true;
     }
     return exist;
  }
    // create the connection , if fail return null
    private Connection connect(){
       Connection tmp =null;
       try
       {
      tmp=DriverManager.getConnection(url+dbFileName);
       }
       catch(Exception e)
       {
         e.printStackTrace();
       }
      return tmp;
    }

    // if fail return -1

    public int update(String sql)
    {
       int rs=-1;
       try
       {
         rs=stat.executeUpdate(sql);
       }
       catch(Exception e)
       {
        e.printStackTrace();
       }
      return rs;
    }

  // use for return a value ,fail return null
    public Object getResult(String sql)
    {
        Object tmp=null;
        try
        {
          result= stat.executeQuery(sql);
          System.out.println(result);
          if(result.next())
          {

             tmp=result.getObject(1);
          }
          else
          {
              tmp="";
          }
          result.close();
        }
        catch(Exception e)
        {
          e.printStackTrace();
        }
      return tmp;
    }

    public String[][] getResults(String sql)
    {
       String[][] rs=null;
       ArrayList tmp=new ArrayList();
       try
       {
        result=stat.executeQuery(sql);
        ResultSetMetaData rsma=result.getMetaData();
        int countColonne=rsma.getColumnCount();
        while(result.next())
        {
           String[] tmpRow=new String[countColonne];
           for(int i=0;i<tmpRow.length;i++)
           {
             tmpRow[i]=result.getString(i+1);
           }
           tmp.add(tmpRow);
        }
        int countRow=tmp.size();
        rs=new String[countRow][countColonne];
        for(int i=0;i<countRow;i++)
        {
          rs[i]=(String[]) tmp.get(i);
        }
       }
       catch(Exception e)
       {
         e.printStackTrace();
       }
      return rs;
    }

    public PreparedStatement getPreparedStat(String sql)
    {
      PreparedStatement ps=null;
      try
      {
         ps=connect.prepareStatement(sql);
      }
      catch(SQLException e)
      {
         e.printStackTrace();
      }
      return ps;
    }

    public void close()
    {
      if(connect!=null)
      {
          try
          {
            connect.close();
            connect=null;
          }
          catch(Exception e)
          {
            e.printStackTrace();
          }
      }
    }
public static void main(String args[])
{
     DBManager db=new DBManager();
     System.out.println("OK test");
     System.out.println(db.getResult("select IdUser from Users where UserName='first'").toString());
     db.close();

}     
 }
